﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using Entities;

namespace DataAccess
{
    public class EmployeeDAL : BaseObject
    {
        #region Constructor

        ///<summary>
        ///Initializes a new instance of the <see cref="UserDal"/> 
        ///class with the specified <see cref="BaseDAL"/>.
        ///</summary>
        public EmployeeDAL()
            : base()
        {

        }

        #endregion

        #region Public Methods
        ///<summary>
        ///Create Obj from ireader.
        ///</summary>

        private Employee GetObjectFromReader(IDataReader reader)
        {
            Employee temp = new Employee();
            temp.UserName = (reader["UserName"] is DBNull) ? string.Empty : (System.String)reader["UserName"];
            temp.Password = (reader["Password"] is DBNull) ? string.Empty : (System.String)reader["Password"];
            temp.Email = (reader["Email"] is DBNull) ? string.Empty : (System.String)reader["Email"];
            temp.Type = (reader["Type"] is DBNull) ? int.MinValue : (System.Int32)reader["Type"];
            return temp;
        }

        ///<summary>
        ///Get List User and store in a list 
        ///</summary>
        public List<Employee> GetList_Employee(string whereSql, string orderBy)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelectEmployeesDynamic, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@WhereCondition", whereSql));
                cmd.Parameters.Add(new SqlParameter("@OrderByExpression", orderBy));
                conn.Open();
                List<Employee> lst = new List<Employee>();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        lst.Add(GetObjectFromReader(reader));
                    }
                }
                conn.Close();
                return lst;
            }
        }

        ///<summary>
        ///Get All User and store in a list 
        ///</summary>
        public List<Employee> GetAll_Employee()
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelectEmployeesAll, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                List<Employee> lst = new List<Employee>();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        lst.Add(GetObjectFromReader(reader));
                    }
                }
                conn.Close();
                return lst;
            }
        }

        ///<summary>
        ///Get List Employee by UserName
        ///</summary>
        public Employee Get_Employee_By_ID(string userName)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelectEmployeesByID, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@UserName", userName));
                conn.Open();
                Employee temp = new Employee();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        temp = GetObjectFromReader(reader);
                    }
                }
                conn.Close();
                return temp;
            }
        }

        ///<summary>
        ///Using for paging data 
        ///</summary>
        /// <param name="pageIndex">Number of pages</param>
        /// <param name="PageSize">Number of a rows per page</param>
        /// <param name="conditions">Where  fields</param>
        /// <param name="groupBy">Group by fields</param>
        public List<Employee> Get_EmployeeAsPaging(string sWhere, string sOrderBy, int pageIndex, int pageSize)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_SelectEmployeesPaged, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Where", sWhere));
                cmd.Parameters.Add(new SqlParameter("@OrderBy", sOrderBy));
                cmd.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
                cmd.Parameters.Add(new SqlParameter("@PageSize", pageSize));
                
                try
                {
                    conn.Open();
                    List<Employee> lst = new List<Employee>();
                    using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            lst.Add(GetObjectFromReader(reader));
                        }
                    }
                    conn.Close();
                    return lst;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        ///<summary>
        ///Insert new User
        ///</summary>
        public bool Insert_Employee(Employee temp)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_InsertEmployee, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@UserName", temp.UserName));
                cmd.Parameters.Add(new SqlParameter("@Password", temp.Password));
                cmd.Parameters.Add(new SqlParameter("@Email", temp.Email));
                cmd.Parameters.Add(new SqlParameter("@Type", temp.Type));

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    return false;
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
                return true;
            }
        }

        ///<summary>
        ///Update a User
        ///</summary>
        public bool Update_Employee(Employee temp)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_UpdateEmployee, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@UserName", temp.UserName));
                cmd.Parameters.Add(new SqlParameter("@Password", temp.Password));
                cmd.Parameters.Add(new SqlParameter("@Email", temp.Email));
                cmd.Parameters.Add(new SqlParameter("@Type", temp.Type));

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    return false;
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
                return true;
            }
        }

        public bool Delete_Employee(Employee temp)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(usp_DeleteEmployee, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@UserName", temp.UserName));

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException ex)
                {
                    return false;
                    throw ex;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
                return true;
            }
        }

        #endregion
    }
}
